import sqlite3
from flask import Flask, request, jsonify, render_template, make_response
import os
from datetime import datetime

app = Flask(__name__)

# 数据库初始化
def init_database():
    conn = sqlite3.connect('transfers.db')
    cursor = conn.cursor()
    
    # 创建转账记录表
    cursor.execute('''
CREATE TABLE IF NOT EXISTS transfers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sender TEXT,
    receiver TEXT,
    amount REAL,
    remark TEXT,
    timestamp DATETIME
)
''')
    
    conn.commit()
    conn.close()

# 初始化数据库（首次运行时执行）
if not os.path.exists('transfers.db'):
    init_database()

# 获取数据库连接
def get_db_connection():
    conn = sqlite3.connect('transfers.db')
    conn.row_factory = sqlite3.Row
    return conn

# API: 获取所有转账记录
@app.route('/api/transfers', methods=['GET'])
def get_transfers():
    conn = get_db_connection()
    transfers = conn.execute('SELECT * FROM transfers ORDER BY timestamp DESC').fetchall()
    conn.close()
    
    # 转换为字典列表
    result = []
    for transfer in transfers:
        result.append({
            'id': transfer['id'],
            'sender': transfer['sender'],
            'receiver': transfer['receiver'],
            'amount': transfer['amount'],
            'remark': transfer['remark'],
            'timestamp': transfer['timestamp']
        })
    
    return jsonify({'success': True, 'transfers': result})

# API: 添加转账记录
@app.route('/api/transfers', methods=['POST'])
def add_transfer():
    data = request.get_json()
    
    # 验证必要字段
    if not all(k in data for k in ('sender', 'receiver', 'amount')):
        return jsonify({'success': False, 'error': '缺少必要字段'}), 400
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 插入新记录
    cursor.execute(
        'INSERT INTO transfers (sender, receiver, amount, remark, timestamp) VALUES (?, ?, ?, ?, ?)',
        (data['sender'], data['receiver'], data['amount'], data.get('remark', ''), datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
    )
    
    conn.commit()
    conn.close()
    
    return jsonify({'success': True, 'message': '转账记录添加成功'})

# 路由: 转账记录页面
@app.route('/transfers_history')
def transfers_history():
    return render_template('transfers_history.html')

# 示例调用函数
def add_sample_transfers():
    """添加示例转账记录"""
    # 示例转账数据列表，格式：(sender, receiver, amount, remark)
    transfers = [
        ("糕辰鲤雪缪", "张三", 200.0, "生活费"),
        ("糕辰鲤雪缪", "李四", 150.50, "借款"),
        ("糕辰鲤雪缪", "王五", 300.0, "项目经费")
    ]
    
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        for transfer in transfers:
            sender, receiver, amount, remark = transfer
            cursor.execute(
                'INSERT INTO transfers (sender, receiver, amount, remark, timestamp) VALUES (?, ?, ?, ?, ?)',
                (sender, receiver, amount, remark, datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
            )
        conn.commit()
        print(f"✅ 成功添加 {len(transfers)} 条示例转账记录")
    except Exception as e:
        print(f"❌ 添加示例记录失败: {e}")
    finally:
        conn.close()

if __name__ == '__main__':
    # 添加示例转账记录（类似学生信息添加格式）
    add_sample_transfers()
